import re
from urllib import request
from MysqlUtils import *
import pymysql

'''
批量插入mysql
'''


class SpiderTest(object):
    # 数据库链接实例化
    global conn ,cursor#    全局变量 也可以用self.conn调用
    mysql = MysqlUtils()
    conn = mysql.get_mysql_connection()
    # 创建游标
    cursor = conn.cursor()
    
    url = 'https://coding.imooc.com/'
    url2='https://coding.imooc.com/?sort=0&unlearn=0&page=2'
    div_pattern = '<div class=\'shizhan-skill clearfix\'>([\s\S]*?)</div>'
    name_pattern = 'class="">([\s\S]*?)</a>\\n'

    # 抓取页面
    def __fetch_content(self):
        opUrl = request.urlopen(SpiderTest.url2)
        htmls = opUrl.read()
        htmls = str(htmls, encoding='utf-8')
        # print(htmls)
        return htmls

    # 正则分析
    def __analysis(self, htmls):
        root_html = re.findall(SpiderTest.div_pattern, htmls)
        anchors = []
        reslutNames = []
        id = 1
        for html in root_html:
            name = re.findall(SpiderTest.name_pattern, html)
            anchor = {'name': name}
            anchors.append(anchor)
        ckName = anchors[0]['name']
        for reslutName in ckName:
            reslutName = {"id": str(id), "name": reslutName}
            reslutNames.append(reslutName)
            id += 1
        return reslutNames

    def go(self):
        root_html = self.__fetch_content()
        nameList = self.__analysis(root_html)
        1 == 1
        # print(nameList, end='')
        values = []
        valueStrings = []
        # print(nameList)
        for i in range(len(nameList)):
            #  onename = nameList[i]['name']
            course_name = nameList[i]['name']
            values.append(course_name)

        # print('名称：', course_name)

        try:
            # self.cursor.execute('insert into spidertest(name) values(%s)', course_name)
            for key in range(len(values)):
                valueStrings.append(values[key])
                # 提交
            print('valueStrings=',valueStrings)
            cursor.executemany('insert into spidertest(name) values(%s)', valueStrings)
            conn.commit()
        except:
            conn.rollback()
            print('插入数据失败：')
        #  print(values)
        print(valueStrings)
        # 关闭
        conn.close()

    # corusor=conn["cursor"]
    # print(corusor)

    # def __get_connection(self):
    #     cursor=self.cursor
    #     db=self.db
    #     print(cursor,db)
    #     result = MysqlUtils.__get_connection()

    # db = spiderTest.db
    # cursor = SpiderTest.cursor
    # for i in range(len(nameList)):
    #     onename = [nameList[i]['id'], nameList[i]['name']]
    #     name = nameList[i]['name']
    #     print(onename)
    #     try:
    #         cursor.execute('insert into spidertest(name) values(%s)', name)
    #         # 提交
    #         db.commit()
    #     except:
    #         db.rollback()
    #         print('插入数据失败：')
    #
    # # 关闭
    # db.close()
    # print(html)


spiderTest = SpiderTest()

spiderTest.go()
